{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# 天猫商城销售数据分析\n",
    "对天猫订单成交数据进行相关清洗和分析，发现问题，提出建议。\n",
    "* 总销售额和订单,   趋势\n",
    "* 加大销量，活动应该安排在周几\n",
    "* 应该重点关注哪些地区\n",
    "* 大部分消费者的消费区间\n",
    "* 订单购买率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "import matplotlib as mplt\n",
    "mplt.rcParams['font.sans-serif'] = ['KaiTi']\n",
    "mplt.rcParams['font.serif'] = ['KaiTi']\n",
    "# mpl.rcParams['axes.unicode_minus'] = False # 解决保存图像是负号'-'显示为方块的问题,或者转换负号为字符串\n",
    "\n",
    "# import seaborn as sns\n",
    "# sns.set_style(\"darkgrid\",{\"font.sans-serif\":['KaiTi', 'Arial']})   #这是方便seaborn绘图得时候得字体设置"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "path_data = 'tmall_sale_data_2020.xlsx'\n",
    "data = pd.read_excel(path_data, index_col=0)\n",
    "data.describe()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "print(data.head())\n",
    "#sheet_name='Sheet2'"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "数据描述\n",
    "* 数据量 28009\n",
    "* 列名：\n",
    " 1. 订单编号\n",
    " 2. 总金额:         范围（1-16065）\n",
    " 3. 买家实际支付金额: 范围（0-16065）\n",
    " 4. 收货地址:       无空值\n",
    " 5. 订单创建时间：   无空值，范围(2020-02-01 00:14:15 , 2020-02-29 23:59:18)\n",
    " 6. 订单付款时间:   有空值，范围（2020-02-01 00:14:20 , 2020-03-01 19:25:42）\n",
    " 7. 退款金额:      (0- 3800)\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# 数据清洗\n",
    "data['收货地址'].isnull().any()# 空值"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "data['订单付款时间'].isnull().any()# 空值空值"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "data[data['总金额']<0].count() # 范围"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "data['总金额'].between(0,10).any() # 范围"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "data['总金额'].between(-100,0).any() # 范围"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "data.min()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# 销售额和订单趋势\n",
    "# 1. 总量\n",
    "print('销售额总量', data['总金额'].sum())"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# 2. 月按日趋势\n",
    "print(data['订单付款时间'].dtype) # datetime64[ns]\n",
    "data.dtypes"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "data['day'] = data['订单创建时间'].apply(lambda x : x.day)\n",
    "data['hour'] = data['订单创建时间'].apply(lambda x : x.hour)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "group_day = data.groupby(by=['day'])['总金额'].sum()\n",
    "group_day.plot.bar()\n",
    "print(group_day)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "count_day = data.groupby(by=['day'])['day'].count()\n",
    "count_day.plot.bar()\n",
    "print(count_day)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# 3. 日按时趋势\n",
    "group_hour = data.groupby(by=['hour'])['总金额'].sum()\n",
    "group_hour.plot.bar()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "count_hour = data.groupby(by=['hour'])['hour'].count()\n",
    "count_hour.plot.bar()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# 每周的周几最好\n",
    "data['week'] = data['订单创建时间'].apply(lambda x : x.weekday() +1)\n",
    "print(data.head())\n",
    "group_week = data.groupby(by=['week'])['总金额'].sum()\n",
    "group_week.plot.bar()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "count_week = data.groupby(by=['week'])['week'].count()\n",
    "count_week.plot.bar()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# 地区排名\n",
    "group_city = data.groupby(by=['收货地址'])['总金额'].sum()\n",
    "group_city = group_city.sort_values(ascending=True)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "fig = plt.figure(figsize=(8,10)) #新建画布\n",
    "group_city.plot.barh()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# 消费区间\n",
    "import math\n",
    "data['consume_range'] = data['总金额'].apply(lambda x : math.ceil(x/10) * 10 if x < 500 else 600)\n",
    "new_data = data.sort_values(by=['consume_range'])\n",
    "print(new_data.head())\n",
    "group_consume_range = new_data.groupby(by=['consume_range'])['consume_range'].count()\n",
    "# group_consume_range = group_consume_range.sort_index(ascending=True)\n",
    "fig = plt.figure(figsize=(8,10)) #新建画布\n",
    "group_consume_range.plot.barh()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# 消费区间\n",
    "import math\n",
    "data['consume_range'] = data['总金额'].apply(lambda x : math.ceil(x/20) * 20 if x < 500 else 600)\n",
    "group_consume_range = data.groupby(by=['consume_range'])['consume_range'].count()\n",
    "group_consume_range = group_consume_range.sort_index(ascending=True)\n",
    "fig = plt.figure(figsize=(8,10)) #新建画布\n",
    "group_consume_range.plot.pie(autopct='%.2f%%',)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# 订单付款率\n",
    "# 订单数 付款数 退款数\n",
    "bill_count = data['订单创建时间'].count()\n",
    "print('订单数',bill_count)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "pay_count = data['订单付款时间'].count()\n",
    "print('支付数',pay_count)\n",
    "\n",
    "pay2_count = data[data['买家实际支付金额']>0]['订单创建时间'].count()\n",
    "print('支付数',pay2_count)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "return_count = data[data['退款金额']>0]['订单创建时间'].count()\n",
    "print('退款数',return_count)\n",
    "print('退款比例',return_count / pay_count * 100)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "return_all_count = data[(data['退款金额']>0) & (data['退款金额'] == data['总金额'])]['订单创建时间'].count()\n",
    "print('全额退款数',return_all_count)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "buy = pd.Series([return_all_count,return_count-return_all_count,bill_count - pay_count,pay_count-return_count],\n",
    "                index=['全额退款', '部分退款退款','未支付','支付未退款'])\n",
    "buy.plot.pie(autopct='%.2f%%',)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}